﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using SAServices.Model;
using System.Data;

namespace SAServices.DAL
{
    public class ParameterDAL
    {
        internal static bool AddFamilyParameters(Model.ConnectionConext connCtx, List<Model.ParameterInfo> familyParameters)
        {
            string sql = "INSERT INTO Parameters Values(@ParamID, @FamilyID, @Name, @DisplayName, " +
            "@RefKey, @ProRefKey, @Unit, @Value, @IsKey, @IsDriver, @IsCustom, @Tag, @Idx);";

            SqlConnection conn = connCtx.GetOpenSqlConn();
            using (SqlCommand command = conn.CreateCommand())
            {
                command.CommandText = sql;
                foreach (ParameterInfo tempInfo in familyParameters)
                {
                    command.Parameters.Clear();
                    command.Parameters.Add(new SqlParameter("@ParamID", tempInfo.ID));
                    command.Parameters.Add(new SqlParameter("@FamilyID", tempInfo.FamilyID));
                    command.Parameters.Add(new SqlParameter("@Name", tempInfo.Name));
                    command.Parameters.Add(new SqlParameter("@DisplayName", tempInfo.DisplayName));
                    command.Parameters.Add(new SqlParameter("@RefKey", tempInfo.RefKey));
                    command.Parameters.Add(new SqlParameter("@ProRefKey", tempInfo.ProRefKey));
                    command.Parameters.Add(new SqlParameter("@Unit", tempInfo.Unit));
                    command.Parameters.Add(new SqlParameter("@Value", tempInfo.Value));
                    command.Parameters.Add(new SqlParameter("@IsKey", tempInfo.IsKey));
                    command.Parameters.Add(new SqlParameter("@IsDriver", tempInfo.IsDriver));
                    command.Parameters.Add(new SqlParameter("@IsCustom", tempInfo.IsCustom));
                    command.Parameters.Add(new SqlParameter("@Tag", tempInfo.Tag));
                    command.Parameters.Add(new SqlParameter("@Idx", tempInfo.Idx));
                    command.ExecuteNonQuery();
                }
            }

            return true;
        }

        internal static bool UpdateFamilyParameters(Model.ConnectionConext connCtx, List<Model.ParameterInfo> familyParameters)
        {
            string sql = "UPDATE Parameters SET DisplayName = @DisplayName, IsKey = @IsKey, IsCustom = @IsCustom ,Tag = @Tag, Idx = @Idx WHERE ID = @ParamID AND FamilyID = @FamilyID;";

            SqlConnection conn = connCtx.GetOpenSqlConn();
            using (SqlCommand command = conn.CreateCommand())
            {
                command.CommandText = sql;
                foreach (ParameterInfo tempInfo in familyParameters)
                {
                    command.Parameters.Clear();
                    command.Parameters.Add(new SqlParameter("@ParamID", tempInfo.ID));
                    command.Parameters.Add(new SqlParameter("@FamilyID", tempInfo.FamilyID));
                    //command.Parameters.Add(new SqlParameter("@Name", tempInfo.Name));
                    command.Parameters.Add(new SqlParameter("@DisplayName", tempInfo.DisplayName));
                    //command.Parameters.Add(new SqlParameter("@RefKey", tempInfo.RefKey));
                    //command.Parameters.Add(new SqlParameter("@ProRefKey", tempInfo.ProRefKey));
                    //command.Parameters.Add(new SqlParameter("@Unit", tempInfo.Unit));
                    //command.Parameters.Add(new SqlParameter("@Value", tempInfo.Value));
                    command.Parameters.Add(new SqlParameter("@IsKey", tempInfo.IsKey));
                    //command.Parameters.Add(new SqlParameter("@IsDriver", tempInfo.IsDriver));
                    command.Parameters.Add(new SqlParameter("@IsCustom", tempInfo.IsCustom));
                    command.Parameters.Add(new SqlParameter("@Tag", tempInfo.Tag));
                    command.Parameters.Add(new SqlParameter("@Idx", tempInfo.Idx));
                    command.ExecuteNonQuery();
                }
            }

            return true;
        }

        internal static bool DeleteFamilyParameters(Model.ConnectionConext connCtx, Guid familyId)
        {
            string sql = String.Format("DELETE FROM Parameters WHERE FamilyID = '{0}'", familyId);
            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static ParameterInfo GetFamilyParameterInfo(ConnectionConext connCtx, Guid parameterId)
        {
            ParameterInfo retInfo = new ParameterInfo();

            string sql = String.Format("SELECT * FROM Parameters WHERE ID = '{0}';", parameterId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
               
                retInfo.ID = new Guid(Convert.ToString(dt.Rows[0]["ID"]));
                retInfo.FamilyID = new Guid(Convert.ToString(dt.Rows[0]["FamilyID"]));
                retInfo.Name = Convert.ToString(dt.Rows[0]["Name"]);
                retInfo.DisplayName = Convert.ToString(dt.Rows[0]["DisplayName"]);
                retInfo.Idx = Convert.ToInt32(dt.Rows[0]["Idx"]);
                retInfo.IsCustom = Convert.ToBoolean(dt.Rows[0]["IsCustom"]);
                retInfo.IsDriver = Convert.ToBoolean(dt.Rows[0]["IsDriver"]);
                retInfo.IsKey = Convert.ToBoolean(dt.Rows[0]["IsKey"]);
                retInfo.ProRefKey = Convert.ToString(dt.Rows[0]["ProRefKey"]);
                retInfo.RefKey = Convert.ToString(dt.Rows[0]["RefKey"]);
                retInfo.Tag = Convert.ToString(dt.Rows[0]["Tag"]);
                retInfo.Unit = Convert.ToString(dt.Rows[0]["Unit"]);
                retInfo.Value = Convert.ToString(dt.Rows[0]["Value"]);
            }

            return retInfo;

        }

        internal static List<ParameterInfo> GetFamilyKeyParameters(ConnectionConext connCtx, Guid familyId)
        {
            List<ParameterInfo> retList = new List<ParameterInfo>();

            string sql = String.Format("SELECT * FROM Parameters WHERE FamilyID = '{0}' AND IsKey = 1 ORDER BY Idx;",familyId);

           DataTable dt = GenDAL.GetTable(connCtx, sql);

           if (dt.Rows.Count > 0)
           {
               int cnt = dt.Rows.Count;
               for (int i = 0; i < cnt; i++)
               {
                   ParameterInfo retInfo = new ParameterInfo();
                   retInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                   retInfo.FamilyID = familyId;
                   retInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                   retInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                   retInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                   retInfo.IsCustom = Convert.ToBoolean(dt.Rows[i]["IsCustom"]);
                   retInfo.IsDriver = Convert.ToBoolean(dt.Rows[i]["IsDriver"]);
                   retInfo.IsKey = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
                   retInfo.ProRefKey = Convert.ToString(dt.Rows[i]["ProRefKey"]);
                   retInfo.RefKey = Convert.ToString(dt.Rows[i]["RefKey"]);
                   retInfo.Tag = Convert.ToString(dt.Rows[i]["Tag"]);
                   retInfo.Unit = Convert.ToString(dt.Rows[i]["Unit"]);
                   retInfo.Value = Convert.ToString(dt.Rows[i]["Value"]);
                   retList.Add(retInfo);
               }
           }

            return retList;

        }

        internal static List<ParameterInfo> GetFamilyParameters(ConnectionConext connCtx, Guid familyId)
        {
            List<ParameterInfo> retList = new List<ParameterInfo>();

            string sql = String.Format("SELECT * FROM Parameters WHERE FamilyID = '{0}';", familyId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                int cnt = dt.Rows.Count;
                for (int i = 0; i < cnt; i++)
                {
                    ParameterInfo retInfo = new ParameterInfo();
                    retInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                    retInfo.FamilyID = familyId;
                    retInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                    retInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                    retInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                    retInfo.IsCustom = Convert.ToBoolean(dt.Rows[i]["IsCustom"]);
                    retInfo.IsDriver = Convert.ToBoolean(dt.Rows[i]["IsDriver"]);
                    retInfo.IsKey = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
                    retInfo.ProRefKey = Convert.ToString(dt.Rows[i]["ProRefKey"]);
                    retInfo.RefKey = Convert.ToString(dt.Rows[i]["RefKey"]);
                    retInfo.Tag = Convert.ToString(dt.Rows[i]["Tag"]);
                    retInfo.Unit = Convert.ToString(dt.Rows[i]["Unit"]);
                    retInfo.Value = Convert.ToString(dt.Rows[i]["Value"]);
                    retList.Add(retInfo);
                }
            }

            return retList;

        }

        internal static List<ParameterInfo> GetFamilyDriveParameters(ConnectionConext connCtx, Guid familyId)
        {
            List<ParameterInfo> retList = new List<ParameterInfo>();

            string sql = String.Format("SELECT * FROM Parameters WHERE FamilyID = '{0}' AND IsDriver = 1;", familyId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                int cnt = dt.Rows.Count;
                for (int i = 0; i < cnt; i++)
                {
                    ParameterInfo retInfo = new ParameterInfo();
                    retInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                    retInfo.FamilyID = familyId;
                    retInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                    retInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                    retInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                    retInfo.IsCustom = Convert.ToBoolean(dt.Rows[i]["IsCustom"]);
                    retInfo.IsDriver = Convert.ToBoolean(dt.Rows[i]["IsDriver"]);
                    retInfo.IsKey = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
                    retInfo.ProRefKey = Convert.ToString(dt.Rows[i]["ProRefKey"]);
                    retInfo.RefKey = Convert.ToString(dt.Rows[i]["RefKey"]);
                    retInfo.Tag = Convert.ToString(dt.Rows[i]["Tag"]);
                    retInfo.Unit = Convert.ToString(dt.Rows[i]["Unit"]);
                    retInfo.Value = Convert.ToString(dt.Rows[i]["Value"]);
                    retList.Add(retInfo);
                }
            }

            return retList;

        }

        internal static List<ParameterValueInfo> GetParamVals(ConnectionConext connCtx, Guid parameterId, List<ParameterValueInfo> paramConstrains)
        {
            List<ParameterValueInfo> retList = new List<ParameterValueInfo>();

            string tblName = GetFamilyTableNameByParameterId(connCtx, parameterId);

            string sql = String.Format("SELECT DISTINCT [{0}] FROM [{1}] WHERE 1=1 ", parameterId, tblName);

            foreach (ParameterValueInfo item in paramConstrains)
            {
                sql += String.Format("AND [{0}]='{1}' ", item.ID, item.Value);
            }
            sql += ";";

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ParameterValueInfo retInfo = new ParameterValueInfo();
                    retInfo.ID = parameterId;
                    retInfo.Value = Convert.ToString(dt.Rows[i][0]);
                    retList.Add(retInfo);
                }
            }

            return retList;

        }

        internal static ParameterValueInfo GetParamVal(ConnectionConext connCtx, Guid familyId, Guid instanceId, Guid parameterId)
        {
            ParameterValueInfo retValInfo = new ParameterValueInfo();
            retValInfo.ID = parameterId;

            string tblName = GetFamilyTableNameByFamilyId(familyId);
            string sql = String.Format("SELECT DISTINCT [{0}] FROM [{1}] WHERE instanceID='{2}' ", parameterId, tblName, instanceId);

            Object retObj = GenDAL.ExecuteScalar(connCtx, sql);

            if (retObj != null)
            {
                retValInfo.Value = Convert.ToString(retObj);
            }

            return retValInfo;
        }

        private static string GetFamilyTableNameByParameterId(ConnectionConext connCtx, Guid parameterId)
        {
            
            string sql = "SELECT TOP 1 FamilyID FROM Parameters WHERE ID = '{0}'";
            sql = String.Format(sql, parameterId);
            Object retObj = GenDAL.ExecuteScalar(connCtx, sql);

            return String.Format("ZFT_{0}", Convert.ToString(retObj).ToUpper());
        }

        private static string GetFamilyTableNameByFamilyId(Guid familyId)
        {
            return String.Format("ZFT_{0}", familyId.ToString().ToUpper());
        }

        internal static List<ParameterValueInfo> GetDriveParameterVals(ConnectionConext connCtx,Guid familyId, Guid instanceId)
        {
            List<ParameterValueInfo> retList = new List<ParameterValueInfo>();

            string tblName = GetFamilyTableNameByFamilyId(familyId);
            List<ParameterInfo> driveParameters = GetFamilyDriveParameters(connCtx, familyId);

            string selectClause = String.Empty;
            foreach (ParameterInfo item in driveParameters)
            {
                selectClause += String.Format(", [{0}]", item.ID);
            }

            string sql = String.Format("SELECT 1 {0} FROM [{1}] WHERE instanceID = '{2}';", selectClause, tblName, instanceId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < driveParameters.Count; i++)
                {
                    ParameterValueInfo retInfo = new ParameterValueInfo();
                    retInfo.ID = driveParameters[i].ID;
                    retInfo.Value = Convert.ToString(dt.Rows[0][Convert.ToString(retInfo.ID)]);
                    retList.Add(retInfo);
                }
            }

            return retList;
        }

    }
}
